package dao

import entity.Client;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ClientDAO implements DAO <Client> {
    
    private Integer ID = 0;
    
    private static final String SQL_INSERT = " INSERT INTO Clients ( name, abbreviation, idNum, idType, registrationDate, contact, contactPosition, city, billingDates, billingDeliveryMethod, billingComments ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    
    private static final String SQL_SELECT = " SELECT * FROM Clients where id= ?";
    
    private static final String SQL_SELECT_PROPERTY = " SELECT * FROM Clients where ? = ?" ;
    
    private static final String SQL_SELECT_All = " SELECT * FROM Clients";
    
    private static final String SQL_UPDATE = " UPDATE Clients SET id = ?, name = ?, abbreviation = ?, idNum = ?, idType = ?, registrationDate = ?, contact = ?, contactPosition = ?, city = ?, billingDates = ?, billingDeliveryMethod = ?, billingComments = ? WHERE  id = ? ";

    private static final String SQL_DELETE = " DELETE FROM Clients WHERE id = ?";
    
    //Ignore the error, Netbeans regards it like so but it's not.
    
    Integer create(Client clt, Connection connect) 
        throws SQLException {

        PreparedStatement ps = null;
        try {
            ps = connect.prepareStatement(SQL_INSERT,Statement.RETURN_GENERATED_KEYS);
            //ps.setInt(1, Client.getId());
            ps.setString(1, clt.getName());
            ps.setString(2, clt.getAbbreviation());
            ps.setInt(3,clt.getIdNum());
            ps.setInt(4,clt.getIdType());
            ps.setDate(5,clt.getRegistrationDate());
            ps.setString(6,clt.getContact());
            ps.setString(7,clt.getContactPosition());
            ps.setString(8,clt.getCity());
            ps.setString(9,clt.getBillingDates());
            ps.setString(10,clt.getBillingDeliveryMethod());
            ps.setString(11,clt.getBillingComments());
            //ps.setInt(12, Client.getId());
            ID = ps.executeUpdate();
        } finally {
            close(ps);
        }
        return ID;
        //TODO implement
    }
    
    Boolean remove(Client clt, Connection connect) throws SQLException {
        Boolean result= false;
        PreparedStatement ps = null;
        try {
            ps = connect.prepareStatement(SQL_DELETE);
            ps.setInt(1, clt.getId());
            ps.executeUpdate();
            result = true;
        } finally {
            close(ps);
        }
        return result;
        //TODO implement
    }
    
    Boolean update(Client clt, Connection connect) throws SQLException {
        PreparedStatement ps = null;
        try {
            ps = connect.prepareStatement(SQL_UPDATE,Statement.RETURN_GENERATED_KEYS);
            ps.setInt(1, clt.getId());
            ps.setString(2, clt.getName());
            ps.setString(3, clt.getAbbreviation());
            ps.setInt(4,clt.getIdNum());
            ps.setInt(5,clt.getIdType());
            ps.setDate(6,clt.getRegistrationDate());
            ps.setString(7,clt.getContact());
            ps.setString(8,clt.getContactPosition());
            ps.setString(9,clt.getCity());
            ps.setString(10,clt.getBillingDates());
            ps.setString(11,clt.getBillingDeliveryMethod());
            ps.setString(12,clt.getBillingComments());
            ps.setInt(13, clt.getId());
            ID = ps.executeUpdate();
            
        } finally {
            close(ps);
        }
        return ID;
        //TODO implement
    }
    
    Client find(Integer id, Connection connect) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connect.prepareStatement(SQL_SELECT);
            ps.setString(1, id);
            rs = ps.executeQuery();
            Client clt = new Client();
            result = null;
            clt = get_client(rs);
            return clt;
        } finally {
            close(rs);
            close(ps);  
        }
        //TODO implement
    }
    
    List findAll(Connection connect) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connect.prepareStatement(SQL_SELECT_All);
            rs = ps.executeQuery();
            List results = new ArrayList();
            Client clt = new Client();
            result = null;
            while (rs.next()) {
                clt = get_client(rs);
                results.add(clt);}
            return result;
        } finally {
            close(rs);
            close(ps);  
        }
        //TODO implement
    }
    
    List select_by_property(String camp, String Value, Connection connect) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connect.prepareStatement(SQL_SELECT_PROPERTY);
            ps.setString(1, camp);
            ps.setString(2, value);
            rs = ps.executeQuery();
            List results = new ArrayList();
            Client clt = new Client();
            result = null;
            while (rs.next()) {
                clt = get_client(rs);
                results.add(clt);}
            return result;

        } finally {
            close(rs);
            close(ps);  
        }
        //TODO implement
    }
        
    Client get_client (ResultSet rs) throws SQLException {
 
        Client clt = new Client();
        clt.setId(rs.getInt("id"));
        clt.setName(rs.getString("name"));
        clt.setAbbreviation(rs.getString("abbreviation"));
        clt.setIdNum(rs.getInt("idNum"));
        clt.setIdType(rs.getInt("idType"));
        clt.setRegistrationDate(rs.getDate("registrationDate"));
        clt.setContact(rs.getString("contact"));
        clt.setContactPosition(rs.getString("contactPosition"));
        clt.setCity(rs.getString("city"));
        clt.setBillingDates(rs.getDate("billingDates"));
        clt.setBillingDeliveryMethod(rs.getString("deliveryMethod"));
        clt.setBillingComments(rs.getString("billingComments"));

        return clt;
    }
}

